Tidy data

• the same data can be representing multiple ways
• the datasets below all show the same values of four variables: country, year, popuplation, and number of tuberculosis cases
  • each dataset organises the values differently

• which is easiest to read?

Why tidy data?

“Happy families are all alike; every unhappy family is unhappy in its own way.” — Leo Tolstoy

“Tidy datasets are all alike, but every untidy dataset is untidy in its own way.” — Hadley Wickham

• data tidying requires some work up front, but is helpful in the long run
• once you have tidy data, you’ll spend less time trying to get your data in the right shape to do what you want

Data tidying

• re-shaping
  • e.g., from wide to long data
• outcome:
  • each column = a variable
  • each row = an observation

Data tidying with the tidyverse

• with tidyr package
  • pivot_longer(): make wide data longer
  • pivot_wider(): make long data wider
• we often need to convert between these formats to do different types of summaries or visualisation

Lengthening data: df_billboard

• in the billboard.csv dataset
  • each row is a song
  • the first three columns are variables that describe the song (artist, track, date_entered)
  • we have 76 columns (wk1-wk76) that describe the rank of the song that week
    • the columns names are one variable (the week), and the cell values are another variable (the rank)

df_billboard %>% 
  head() %>% 
  knitr::kable() %>% 
  kableExtra::kable_styling()

• is this data in Tabelle 1 tidy?
• is this data too wide or too long?
• how might we tidy this data?

pivot_longer()

• pivot_longer()
  • converts a wide data table to a longer format by converting the headers from specified columns into the values of new columns
  • and combining the values of those columns into a new condensed column

df_billboard_tidy <- df_billboard %>% 
  pivot_longer(
    cols = starts_with("wk"), 
    names_to = "week", 
    values_to = "rank"
  )

df_billboard_tidy %>% 
  head(n = 10) %>% 
  knitr::kable() %>% 
  kableExtra::kable_styling(font_size = 20)

• col = specifies which columns need to be pivoted (i.e., which are not variables)
  • takes the same syntax as select(), so we could use e.g., starts_with("")
• names_to = names the variable stored in the current column names, here it is week
• values_to = names the variable stored int he cell values, which we name rank
• N.B., we had to wrap week and rank with quotation marks because they aren’t variable names yet

Removing missing values (NAs)

• we have a few NA (missing) values; this happened when a song was not in the top 100
  • the values_drop_na = TRUE/FALSE argument drops pivoted rows don’t have a value for the new variable

df_billboard_tidy <- df_billboard %>% 
  pivot_longer(
    cols = starts_with("wk"), 
    names_to = "week", 
    values_to = "rank",
    values_drop_na = TRUE
  ) 

• how many rows fewer do we have now?

Parsing numbers

• the data is now tidy, but week still contains wk in the values
• the readr package has a handy function: parse_number() extracts the first number from a string, ignoring all other text
  • how could we use parse_number() to alter the variable week?

df_billboard_tidy <- df_billboard_tidy %>% 
  mutate(week = parse_number(week))

Working with dates

• the tidyverse also has a package that makes working with dates easier: lubridate
  • the variable date_entered has the format year-month-day (ymd)
  • we can use lubridate verbs to extract the year, month, and day

df_billboard_tidy <- df_billboard_tidy %>%
  mutate(
    month = month(date_entered, label = F),
    day = day(ymd(date_entered)),
    year = year(ymd(date_entered))
  ) 

Plotting our tidy data

• now we have the week data in one variable and the rank data in another variable
• let’s try to plot billboard ranks by week

Abbildung 2: Billboard ranks by number of weeks for songs that were in the top 100 in the year 2000

• what is the most number of weeks a song was in the top 100?
• why is 100 at the bottom and 0 at the top?
• why is the bottom right quarter empty? What does this mean?

Widening data: df_cms

• pivot_wider() make datasets wider by increasing columns and reducing rows
  • this helps when one observation is spread across multiple rows
  • this type of data isn’t very common in the wild, but is pretty common in governmental data

pivot_wider()

• takes long data and makes it wider
• takes a few arguments:
  • id_cols: identifying columns
  • names_from: what should we call the new column containing the previous column names?
  • names_prefix: prefix for the new column names
  • values_from: new column values